package org.oexam.ostudy.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.TreeSet;

import javax.naming.NamingException;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.oexam.ostudy.facade.struct.TestNameAndId;
import org.oexam.ostudy.misc.GeneralConstants;
import org.oexam.ostudy.struct.Questions;

import org.oexam.ostudy.struct.AllResults;
import org.oexam.ostudy.struct.StudentAnswers;
import org.oexam.ostudy.struct.TestAtt;

public class TestAccess {

	public int getLastQuestionsID() throws SQLException, NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery("select max(qid) as qid from Questions");

		int lastID = 0;
		while (rs.next()) {
			lastID = Integer.parseInt((rs.getString("qid")));
		}
		rs.close();

		return lastID;

	}

	public boolean isCalcEnabled() throws SQLException, NamingException {

		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery("select isEnabled from Tools where tid=1");

		boolean isEnabled = true;
		while (rs.next()) {
			isEnabled = (rs.getBoolean("isEnabled"));
		}
		rs.close();

		return isEnabled;
	}

	public void toggleCalc() throws SQLException, NamingException {

		DBConnection dbConnection = new DBConnection();

		boolean isEnabled = this.isCalcEnabled();
		if (isEnabled) {
			dbConnection
					.executeUpdate("update Tools set isEnabled=false where tid=1");
		} else {
			dbConnection
					.executeUpdate("update Tools set isEnabled=true where tid=1");
		}

	}

	public int getLastAttendanceID() throws SQLException, NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery("select max(att_id) as att_id from TestAtt");

		int lastID = 0;
		while (rs.next()) {
			if (null != rs.getString("att_id")) {
				lastID = Integer.parseInt((rs.getString("att_id")));
			}

		}
		rs.close();

		return lastID;
	}

	public int getLastAnswerID() throws SQLException, NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery("select max(aid) as aid from StudentAnswers");

		int lastID = 0;
		while (rs.next()) {
			if (null != rs.getString("aid")) {
				lastID = Integer.parseInt((rs.getString("aid")));
			}
		}
		rs.close();

		return lastID;
	}

	public int getLastResultID() throws SQLException, NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery("SELECT max(result_id) as result_id FROM AllResults;");

		int lastID = 0;
		while (rs.next()) {
			if (null != rs.getString("result_id")) {
				lastID = Integer.parseInt((rs.getString("result_id")));
			}
		}
		rs.close();

		return lastID;
	}

	public void insertQuestion(Questions questions) {
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();

		session.save(questions);

		session.getTransaction().commit();
		session.close();
	}

	public void insertAttendance(TestAtt attendance) {
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();

		session.save(attendance);

		session.getTransaction().commit();
		session.close();
	}

	public void insertStudentAnswer(StudentAnswers studentAnswer) {

		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();

		session.save(studentAnswer);

		session.getTransaction().commit();
		session.close();
	}

	public void insertResult(AllResults result) {
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();

		session.save(result);

		session.getTransaction().commit();
		session.close();
	}

	public List<TestNameAndId> getAllTestsLinkedToTeacher(int teacherID)
			throws SQLException, NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "Tests"
						+ " where teacher=" + teacherID);

		List<TestNameAndId> listOftests = new ArrayList<TestNameAndId>();
		while (rs.next()) {
			TestNameAndId testNameAndId = new TestNameAndId();
			testNameAndId.setId(rs.getInt("test_id"));
			testNameAndId.setTestName(rs.getString("test_name"));
			listOftests.add(testNameAndId);
		}
		rs.close();

		return listOftests;
	}

	public List<TestNameAndId> getAllTests() throws SQLException,
			NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "Tests");

		List<TestNameAndId> listOftests = new ArrayList<TestNameAndId>();
		while (rs.next()) {
			TestNameAndId testNameAndId = new TestNameAndId();
			testNameAndId.setId(rs.getInt("test_id"));
			testNameAndId.setTestName(rs.getString("test_name"));
			listOftests.add(testNameAndId);
		}
		rs.close();

		return listOftests;
	}

	public boolean hasStudentAttandedthisTest(int studentID, int testID)
			throws SQLException, NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "TestAtt"
						+ " where student_id=" + studentID + " and test_id="
						+ testID);

		TestAtt att = new TestAtt();
		while (rs.next()) {

			att.setStudent_id(rs.getInt("student_id"));
			att.setTest_id(rs.getInt("test_id"));

		}
		rs.close();
		if (att.getStudent_id() == studentID && att.getTest_id() == testID) {

			return true;
		}

		return false;
	}

	public List<Questions> getAllQuestionsForATest(int testID)
			throws SQLException, NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "Questions"
						+ " where test_id=" + testID);

		List<Questions> questions = new ArrayList<Questions>();
		while (rs.next()) {
			Questions ques = new Questions();

			ques.setQuestion(rs.getString("question"));
			ques.setAnswer(rs.getInt("answer"));
			ques.setQid(rs.getInt("qid"));
			ques.setOption1(rs.getString("option1"));
			ques.setOption2(rs.getString("option2"));
			ques.setOption3(rs.getString("option3"));
			ques.setOption4(rs.getString("option4"));
			ques.setTest_id(rs.getInt("test_id"));

			questions.add(ques);
		}
		rs.close();

		return questions;
	}

	public boolean isCorrectAnswer(StudentAnswers ans) throws SQLException,
			NamingException {

		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "Questions"
						+ " where qid=" + ans.getQuestionID());

		int actualAns = 0;
		while (rs.next()) {
			actualAns = rs.getInt("answer");
		}

		if (actualAns == ans.getAnswer()) {
			return true;
		}
		return false;
	}

}
